﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

namespace DAL
{
    public class AdDB
    {
        DbCommand cm = null;
        DataSet ds = null;
        SqlDatabase db = null;
        IDataReader dr = null;
        string conStr = ConfigurationManager.ConnectionStrings["ConnectionStringDev"].ToString();

        #region public Methods

        public IDataReader Select_AdCategoryMaster()
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdCategoryMaster"))
                {
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                //ErrHandler.WriteError(ex.Message);
                throw new Exception(ex.Message);
            }
        }

        public int Insert_AdMaster(int UserID, int AdCategoryID, int SubCategoryID, string AdTitle, string ImagePath, string ProductDescription, string LandMark, string State, string Country, int FeaturedAd, int TopAd, int HomepageGallery)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Insert_Ad"))
                {
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@AdCategoryID", DbType.Int32, AdCategoryID);
                    db.AddInParameter(cm, "@SubCategoryID", DbType.Int32, SubCategoryID);
                    db.AddInParameter(cm, "@Title", DbType.String, AdTitle);
                    db.AddInParameter(cm, "@ImagePath", DbType.String, ImagePath);
                    db.AddInParameter(cm, "@ProductDescription", DbType.String, ProductDescription);
                    db.AddInParameter(cm, "@LandMark", DbType.String, LandMark);
                    db.AddInParameter(cm, "@State", DbType.String, State);
                    db.AddInParameter(cm, "@Country", DbType.String, Country); 
                    db.AddInParameter(cm, "@FeaturedAd", DbType.Int32, FeaturedAd);
                    db.AddInParameter(cm, "@TopAd", DbType.Int32, TopAd);
                    db.AddInParameter(cm, "@HomepageGallery", DbType.Int32, HomepageGallery); 
                    dr = db.ExecuteReader(cm);
                    if (!object.Equals(dr, null))
                    {
                        while (dr.Read())
                        {
                            return Convert.ToInt32(dr["AdID"]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //ErrHandler.WriteError(ex.Message);
                throw new Exception(ex.Message);
            }
            return 0;
        }

        public int Update_AdMaster(int UserID, int AdCategoryID, int SubCategoryID, string AdTitle, string ImagePath, string ProductDescription, string LandMark, string State, string Country, int adID)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Update_AdMaster"))
                {
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@AdCategoryID", DbType.Int32, AdCategoryID);
                    db.AddInParameter(cm, "@SubCategoryID", DbType.Int32, SubCategoryID);
                    db.AddInParameter(cm, "@AdTitle", DbType.String, AdTitle);
                    db.AddInParameter(cm, "@ImagePath", DbType.String, ImagePath);
                    db.AddInParameter(cm, "@ProductDescription", DbType.String, ProductDescription);
                    db.AddInParameter(cm, "@LandMark", DbType.String, LandMark);
                    db.AddInParameter(cm, "@State", DbType.String, State);
                    db.AddInParameter(cm, "@Country", DbType.String, Country); 
                    // db.AddInParameter(cm, "@TopAd", DbType.Int32, TopAd); 
                    db.AddInParameter(cm, "@AdID", DbType.Int32, adID);

                    return db.ExecuteNonQuery(cm);

                }
            }
            catch (Exception ex)
            {
                //ErrHandler.WriteError(ex.Message);
                throw new Exception(ex.Message);
            }
            return 0;
        }

        public IDataReader Select_LatestTopFeaturedAd(int TypeID, string CountryID)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_LatestTopFeaturedAd"))
                {
                    cm.CommandTimeout = 200;
                    db.AddInParameter(cm, "@TypeID", DbType.Int32, TypeID);
                    db.AddInParameter(cm, "@CountryID", DbType.String, CountryID);
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataSet Select_AdMaster(int AdID,string ViewType="",int UserID=0,bool IncreseCount=false,int SubCatID=0)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdMaster"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID); 
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@ViewType", DbType.String, ViewType);
                    db.AddInParameter(cm, "@IncreseCount", DbType.Boolean, IncreseCount);
                    db.AddInParameter(cm, "@SubCatID", DbType.Int16, SubCatID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }


        public DataSet Select_RelatedAds(int AdID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_RelatedAds"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Select_AdImageVideos(int AdID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdImageVideos"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Deactivate_AdImageVideos(int AdImageVideosID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Deactivate_AdImageVideos"))
                {
                    db.AddInParameter(cm, "@AdImageVideosID", DbType.Int32, AdImageVideosID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Update_Image(int AdImageVideosID, string Path)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Update_Image"))
                {
                    db.AddInParameter(cm, "@AdImageVideosID", DbType.Int32, AdImageVideosID);
                    db.AddInParameter(cm, "@Path", DbType.String, Path);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Select_SubCategoryMasterDetails(int AdCategoryID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_SubCategoryMasterDetails"))
                {
                    db.AddInParameter(cm, "@AdCategoryID", DbType.Int32, AdCategoryID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Select_AllSubCategoryAds()
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AllSubCategoryAds"))
                {
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }




        public DataSet Select_SubCategoryAds_CatID(int SubCategoryID,int UserID=0,string ViewType="")
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_SubCategoryAds_CatID"))
                {
                    db.AddInParameter(cm, "@SubCategoryID", DbType.Int32, SubCategoryID);
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@ViewType", DbType.String, ViewType);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
        public IDataReader Bind_SubCategoryMasterDetails(int AdCategoryID)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_SubCategoryMasterDetails"))
                {
                    db.AddInParameter(cm, "@AdCategoryID", DbType.Int32, AdCategoryID);
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataSet Bind_AdCategoryMaster()
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdCategoryMaster"))
                {
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Select_AdPostedClientDetails(int AdID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdPostedClientDetails"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public string Insert_ContactDealerDetails(int UserID,string FName, string Email, string Message,int AdID)
        {
            string result = string.Empty;
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Insert_ContactAd"))
                {
                    db.AddInParameter(cm, "@UserID", DbType.String, UserID);
                    db.AddInParameter(cm, "@FName", DbType.String, FName);
                    db.AddInParameter(cm, "@Email", DbType.String, Email);
                    db.AddInParameter(cm, "@Message", DbType.String, Message);
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);

                    db.ExecuteNonQuery(cm);
                    result = "Successfully Saved";
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
                throw new Exception(ex.Message);
            }
            return result;
        }

        public DataSet Select_MembershipType(int ItemID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Select_MembershipType"))
                {
                    db.AddInParameter(cm, "@ItemID", DbType.Int32, ItemID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public IDataReader Insert_PaymentMaster(int UserID, int ItemID, decimal NetPayable, string UserName, string EMailID, string Currency, string Phone, string Purpose)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Insert_PaymentMaster"))
                {
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@ItemID", DbType.Int32, ItemID);
                    db.AddInParameter(cm, "@NetPayable", DbType.Decimal, NetPayable);
                    db.AddInParameter(cm, "@UserName", DbType.String, UserName);
                    db.AddInParameter(cm, "@EMailID", DbType.String, EMailID);
                    db.AddInParameter(cm, "@Currency", DbType.String, Currency);
                    db.AddInParameter(cm, "@Phone", DbType.String, Phone);
                    db.AddInParameter(cm, "@Purpose", DbType.String, Purpose);
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public string Insert_InventoryMaster(int PaymentID, string PaymentStatus, string TransactionID, int UserID, int ItemID)
        {
            string result = string.Empty;
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Insert_InventoryMaster"))
                {
                    db.AddInParameter(cm, "@PaymentID", DbType.Int32, PaymentID);
                    db.AddInParameter(cm, "@PaymentStatus", DbType.String, PaymentStatus);
                    db.AddInParameter(cm, "@TransactionID", DbType.String, TransactionID);
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@ItemID", DbType.Int32, ItemID);
                    //db.AddInParameter(cm, "@NoOfImageUpload", DbType.Int32, NoOfImageUpload);
                    //db.AddInParameter(cm, "@NoOfEmailNotification", DbType.Int32, NoOfEmailNotification);
                    //db.AddInParameter(cm, "@NoOfVideoUpload", DbType.Int32, NoOfVideoUpload);
                    //db.AddInParameter(cm, "@NoOfPersonalPages", DbType.Int32, NoOfPersonalPages);
                    //db.AddInParameter(cm, "@NoOfFeaturedListing", DbType.Int32, NoOfFeaturedListing);
                    //db.AddInParameter(cm, "@NoOfHighlightedListing", DbType.Int32, NoOfHighlightedListing);
                    db.ExecuteNonQuery(cm);
                    result = "Successfully Saved";
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
                throw new Exception(ex.Message);
            }
            return result;
        }

        public IDataReader Select_LocationMaster()
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Select_LocationMAster"))
                {
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataSet Select_AdvancedSearch(string AdTitle, int AdCategoryID, int SubCategoryID, string LandMark,  string ColumnName, string SearchField)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdvancedSearch"))
                {
                    if (AdTitle != string.Empty)
                        db.AddInParameter(cm, "@AdTitle", DbType.String, AdTitle);
                    else
                        db.AddInParameter(cm, "@AdTitle", DbType.String, DBNull.Value);
                    if (AdCategoryID > 0)
                        db.AddInParameter(cm, "@AdCategoryID", DbType.Int32, AdCategoryID);
                    else
                        db.AddInParameter(cm, "@AdCategoryID", DbType.Int32, 0);
                    if (SubCategoryID > 0)
                        db.AddInParameter(cm, "@SubCategoryID", DbType.Int32, SubCategoryID);
                    else
                        db.AddInParameter(cm, "@SubCategoryID", DbType.Int32, 0);
                    if (LandMark != string.Empty)
                        db.AddInParameter(cm, "@LandMark", DbType.String, LandMark);
                    else
                        db.AddInParameter(cm, "@LandMark", DbType.String, DBNull.Value); 
                    if (ColumnName != string.Empty)
                        db.AddInParameter(cm, "@ColumnName", DbType.String, ColumnName);
                    else
                        db.AddInParameter(cm, "@ColumnName", DbType.String, DBNull.Value);
                    if (SearchField != string.Empty)
                        db.AddInParameter(cm, "@SearchField", DbType.String, SearchField);
                    else
                        db.AddInParameter(cm, "@SearchField", DbType.String, DBNull.Value);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Select_UserAds(int UserID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Select_UserAds"))
                {
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public void Activate_Deactivate_Ad(int AdID, int UserID,bool Activate)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Activate_Deactivate_Ad"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@Activate", DbType.Int32, Activate);
                    db.ExecuteNonQuery(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool CheckAd_IsPostedByUser(int AdID, int UserID)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.CheckAd_IsPostedByUser"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    dr = db.ExecuteReader(cm);
                    if (!object.Equals(dr, null))
                    {
                        while (dr.Read())
                        {
                            return Convert.ToBoolean(dr["IsAuthenticate"]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return false;
        }

        public void Insert_AdImageVideosPath(int AdID, string path, bool IsImage, bool IsVideo)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Insert_AdImageVideosPath"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    db.AddInParameter(cm, "@Path", DbType.String, path);
                    db.AddInParameter(cm, "@IsImage", DbType.Boolean, IsImage);
                    db.AddInParameter(cm, "@IsVideo", DbType.Boolean, IsVideo);
                    db.ExecuteNonQuery(cm);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public void Delete_AllVideos(int AdID, bool IsVideo)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Delete_AllVideos"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    // db.AddInParameter(cm, "@IsImage", DbType.Boolean, IsImage);
                    db.AddInParameter(cm, "@IsVideo", DbType.Boolean, IsVideo);
                    db.ExecuteNonQuery(cm);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public string Delete_PaymentMaster(int PaymentID)
        {
            string result = string.Empty;
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Delete_PaymentMaster"))
                {
                    db.AddInParameter(cm, "@PaymentID", DbType.Int32, PaymentID);
                    db.ExecuteNonQuery(cm);
                    result = "Successfully Saved";
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
                throw new Exception(ex.Message);
            }
            return result;
        }

        public string Update_PaymentMaster(int PaymentID, string TransactionID, string PaymentStatus)
        {
            string result = string.Empty;
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Update_PaymentMaster"))
                {
                    db.AddInParameter(cm, "@PaymentID", DbType.Int32, PaymentID);
                    db.AddInParameter(cm, "@TransactionID", DbType.String, TransactionID);
                    db.AddInParameter(cm, "@PaymentStatus", DbType.String, PaymentStatus);
                    db.ExecuteNonQuery(cm);
                    result = "Successfully Saved";
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
                throw new Exception(ex.Message);
            }
            return result;
        }

     
        public IDataReader Select_CountryMaster()
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_CountryMaster"))
                {
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public IDataReader Select_AdLocation_AdID(int AdID)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_AdLocation_AdID"))
                {
                    db.AddInParameter(cm, "@AdID", DbType.Int32, AdID);
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public IDataReader Select_RightAd(int CountryID)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Cld.Select_RightAd"))
                {
                    db.AddInParameter(cm, "@CountryID", DbType.Int32, CountryID);
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion
    }
}